package cn.gson.crm.model.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.po.OfficePo;

public class OfficeDao {
	
	private int bands = 5;
	
	public List<OfficePo> getAllOfficeList(int page,int id , String stucompany) throws SQLException{
		int start = (page - 1) * bands;
		List<Object> params = new ArrayList<>();
		List<Map<String, Object>> listData = null;
		String sql = "SELECT student.student_name,office.* FROM office "
				+ "LEFT OUTER JOIN student ON student.student_id = office.office_student_id";
		sql = buildSqlParams(params, sql, id, stucompany);
		sql +=  " order by office.office_formal_money desc LIMIT ? , ?";
		params.add(start);
		params.add(bands);
		listData = Jdbc.getInstace().executeQueryForList(sql,params.toArray());
		List<OfficePo> officePoList = null;
		if(listData!=null){
			officePoList = buildOfficePo(listData);
		}
		System.out.println(sql+id+stucompany);
		return officePoList;
	}
	
	public boolean updateOfiice(String officeId,String officeCompany,String officeTryMoney,String officeFormalMoney,String officeTime) throws SQLException{
		String sql = "update office set office_company=?,office_try_money=?,"
				+ "office_formal_money=?,office_time=? where office_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, officeCompany,officeTryMoney,officeFormalMoney,officeTime,officeId);
	}
	public boolean updateStudent(String id) throws SQLException{
		String sql = "update student set student_allot_id = -1 where student_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}
	
	public boolean insertOffice(String officeCompany,String officeTryMoney,String officeStudentId,String officeFormalMoney,String officeTime) throws SQLException{
		String sql = "INSERT INTO office (office_company,office_try_money,office_student_id,office_formal_money,office_time) VALUES(?,?,?,?,?)";
		return(Jdbc.getInstace().executeUpdate(sql, officeCompany,officeTryMoney,officeStudentId,officeFormalMoney,officeTime));
	}
	
	
	public String select(int id) throws SQLException{
		String sql = "select office_id from office where office_student_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, id);
		String officeId = null;
		if(map != null ){
			 officeId = map.get("office_id") +"";
			 System.out.println(officeId);
		}
		return officeId;
	}
	public OfficePo selectOffice(String id) throws SQLException{
		String sql = "SELECT student.student_name,office.* FROM office "
				+ "LEFT OUTER JOIN student ON student.student_id = office.office_student_id"
				+ " where office.office_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, id);
		OfficePo officePo = new OfficePo();
		if(map!= null){
			officePo = buildAOfficePo(map);
		}
		return officePo;
	}
	public int getStuId(String name) throws SQLException{
		String sql = "select total_student_id from total where total_student_id in "
				+ "(select student_id from student where student_name = ?)";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, name);
		int id = 0;
		if(map != null){
			id = type(map.get("total_student_id"));
		}
		return id;
	}
	
	public int getOfficePageSum() throws SQLException {
		String sql = "SELECT COUNT(*) as sum from office";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
		int sum = 0;
		if (map != null) {
			sum = Integer.parseInt(map.get("sum").toString());
			sum = sum % bands > 0 ? sum / bands + 1 : sum / bands;
		}
		return sum;
	}
	protected OfficePo buildAOfficePo(Map<String, Object> data){
			OfficePo officePo = new OfficePo();
			officePo.setOfficeId(type(data.get("office_id")));
			officePo.setOfficeStudentId(type(data.get("office_student_id")));
			officePo.setOfficeTryMoney(type(data.get("office_try_money")));
			officePo.setOfficeFormalMoney(type(data.get("office_formal_money")));
			officePo.setStudentName((String)data.get("student_name"));
			officePo.setOfficeCompany((String)data.get("office_company"));
			String s = data.get("office_time").toString().split(" ")[0];
			officePo.setOfficeTime(Date.valueOf(s));
		return officePo;
		}
	
	protected List<OfficePo> buildOfficePo(List<Map<String, Object>> listData){
		List<OfficePo> officePoList = new ArrayList<>();
		for (Map<String, Object> data : listData) {
			OfficePo officePo = new OfficePo();
			officePo.setOfficeId(type(data.get("office_id")));
			officePo.setOfficeStudentId(type(data.get("office_student_id")));
			officePo.setOfficeTryMoney(type(data.get("office_try_money")));
			officePo.setOfficeFormalMoney(type(data.get("office_formal_money")));
			officePo.setStudentName((String)data.get("student_name"));
			officePo.setOfficeCompany((String)data.get("office_company"));
			String s = data.get("office_time").toString().split(" ")[0];
			officePo.setOfficeTime(Date.valueOf(s));
			officePoList.add(officePo);
		}
		
		return officePoList;
		}
	
	
	/**
	 * 字符串转为int
	 * */
	protected int type(Object object) {
		return Integer.parseInt(object.toString());
	}
	
	private String buildSqlParams(List<Object> params, String sql, int id, String stucompany) {
		if (id>0 || !StringUtils.isNullOrEmpty(stucompany)
				|| !StringUtils.isNullOrEmpty(stucompany)) {
			sql += " where";
		}
		if (id>0) {
			sql += " office.office_student_id = ? ";
			params.add(id);
			if (!StringUtils.isNullOrEmpty(stucompany)) {
				sql += " and ";
			}
		}

		if (!StringUtils.isNullOrEmpty(stucompany)) {
			sql += " office.office_company like ? ";
			params.add("%" + stucompany + "%");
		}
		return sql;
	}
	}

